package com.li.commons.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.ResourceBundle;

public class DbUtils {
	private static String driverName = "";
	private static String url="";
	private static String user="";
	private static String password="";
	static {
		ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
		if (bundle == null) {
			throw new IllegalArgumentException("[redis.properties] is not found!");
		}
		driverName = bundle.getString("jdbc.driverClass");
		url = bundle.getString("jdbc.url");
		user = bundle.getString("jdbc.user");
		password = bundle.getString("jdbc.password");
	}
	
	public static List<HashMap> loadListBySql(String sql){
		List<HashMap> resultList = new ArrayList<HashMap>();
		Connection con = null;
		Statement statement = null;
		ResultSet result = null;
		try {
			Class.forName(driverName);
			con = (Connection) DriverManager.getConnection(url, user, password);
			statement = con.createStatement();
			result = statement.executeQuery(sql);
		    ResultSetMetaData rsmd = result.getMetaData();  
		    int fieldCount = rsmd.getColumnCount();  
		    while(result.next()){
		    	HashMap<String, String> itemMap = new HashMap<String, String>();
		        for (int i = 1; i<=fieldCount;i++) {  
		                String fieldName = rsmd.getColumnName(i);  
		                itemMap.put(fieldName, result.getString(fieldName));
		        }  
		        resultList.add(itemMap);
		    }
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(result!=null){
					result.close();
				}
				if(statement!=null){
					statement.close();
				}
				if(con!=null){
					con.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return resultList;
	}
	
	public static HashMap loadByMapSql(String sql){
		HashMap resultMap = new HashMap();
		Connection con = null;
		Statement statement = null;
		ResultSet result = null;
		try {
			Class.forName(driverName);
			con = (Connection) DriverManager.getConnection(url, user, password);
			statement = con.createStatement();
			result = statement.executeQuery(sql);
		    ResultSetMetaData rsmd = result.getMetaData();  
		    int fieldCount = rsmd.getColumnCount();  
		    if(result.next()){
		        for (int i = 1; i<=fieldCount;i++) {  
		                String fieldName = rsmd.getColumnName(i);  
		                resultMap.put(fieldName, result.getString(fieldName));
		        }  
		    }
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(result!=null){
					result.close();
				}
				if(statement!=null){
					statement.close();
				}
				if(con!=null){
					con.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return resultMap;
	}
	
	
	
	public static Connection getConnection() {
        String driverName = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/cms";
        String user = "btc_user";
        String password = "ikongjian*()";
        Connection conn = getConnection(driverName, url, user, password);
        return conn;
    }

    public static Connection getConnection(String driverName, String url, String user, String passwd) {
        Connection conn = null;
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, passwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void setAutoCommit(Connection conn, boolean flag) {
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }

    public static PreparedStatement prepareStatement(Connection conn, String sql) {

        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return preparedStatement;
    }

    public static void executeBatch(PreparedStatement preparedStatement) {
        try {
            preparedStatement.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void setString(PreparedStatement prepstmt, int index, String value) {
        try {
            prepstmt.setString(index, value);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void setInt(PreparedStatement prepstmt, int index, int value) {
        try {
            prepstmt.setInt(index, value);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void setBoolean(PreparedStatement prepstmt, int index, boolean value) {
        try {
            prepstmt.setBoolean(index, value);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void setLong(PreparedStatement prepstmt, int index, long value) {
        try {
            prepstmt.setLong(index, value);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void setFloat(PreparedStatement prepstmt, int index, float value) {
        try {
            prepstmt.setFloat(index, value);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void addBatch(PreparedStatement prepstmt) {
        try {
            prepstmt.addBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void commit(Connection connection) {
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeStatment(PreparedStatement preparedStatement) {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void execute(PreparedStatement deletePs) {
        try {
            deletePs.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
